﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace ResultsTestFixtures
{
    /// <summary>
    /// FitNesse fixture to allow for FitNesse to verify an AwardsSheet
    /// is correct.
    /// </summary>
    public class AwardsSheetFixture : fit.ColumnFixture
    {
        private AwardsSheet _as;
        private DataTable _mensAwardsSheet;
        private DataTable _womensAwardsSheet;
        
        public string Place;
        public string Gender;

        public AwardsSheetFixture()
        {
            const int raceIdOfMenTestRace = 38;
            const int raceIdOfWomenTestRace = 39;

            _as = new AwardsSheet(raceIdOfMenTestRace, "Mens");
            _mensAwardsSheet = _as.OverallAwardsSheet();

            _as = new AwardsSheet(raceIdOfWomenTestRace, "Womens");
            _womensAwardsSheet = _as.OverallAwardsSheet();

        }

        private DataTable getAwardsSheetBasedOnGender()
        {
            if (Gender == "Mens")
                return _mensAwardsSheet;
            else 
                return _womensAwardsSheet;
        }

        public string Name()
        {
            DataTable awardsSheet = getAwardsSheetBasedOnGender();
            return awardsSheet.Rows[Int32.Parse(Place) - 1]["name"].ToString().Trim();
        }

        public string Time()
        {
            DataTable awardsSheet = getAwardsSheetBasedOnGender();
            return awardsSheet.Rows[Int32.Parse(Place) - 1]["time"].ToString();
        }

        public string Bib()
        {
            DataTable awardsSheet = getAwardsSheetBasedOnGender();
            return awardsSheet.Rows[Int32.Parse(Place) - 1]["bib"].ToString();
        }

        public string Age()
        {
            DataTable awardsSheet = getAwardsSheetBasedOnGender();
            return awardsSheet.Rows[Int32.Parse(Place) - 1]["age"].ToString();
        }
    }

    /// <summary>
    /// Produces an Awards Sheet for the race. 
    /// The Awards Sheet is used by the race director
    /// at the awards ceremony to give awards.
    /// </summary>
    public class AwardsSheet
    {
        private SqlConnection _cn;
        private int _raceId;
        private char _gender;

        public AwardsSheet(int raceId, string gender)
        {
            _raceId = raceId;
            if (gender == "Mens")
            {
                _gender = 'm';
            }
            else
            {
                _gender = 'f';
            }

            string connectionString =
                ConfigurationManager.ConnectionStrings["HuberRace"].ConnectionString;
            _cn = new SqlConnection(connectionString);
            _cn.Open();
        }

        // Returns a data table that's the overall men's sheet
        public DataTable OverallAwardsSheet()
        {
            var cmd = new SqlCommand(@"
                SELECT TOP 3 name,time,age,bib,gender
                FROM [ResultsWithAGForUSATF]
                WHERE 
                    raceId = @raceId
                    AND gender = @gender 
                ORDER BY place", _cn);
            cmd.Parameters.AddWithValue("@raceId", _raceId);
            cmd.Parameters.AddWithValue("@gender", _gender);

            var dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            return dt;
        }
    }
}
